<?php
namespace Admin\Controller;
use Think\Controller;

class ExportController extends Controller

{

	public function exportExcel($expTitle,$expCellName,$expTableData){
		$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
		$fileName = $xlsTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
		$cellNum = count($expCellName);
		$dataNum = count($expTableData);

		vendor("PHPExcel.PHPExcel");
		$objPHPExcel = new \PHPExcel();
		$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        // dump($cellName[$cellNum-1]);exit();
		$objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);// 居中

		for($i=0;$i<$cellNum;$i++){
			$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
		}
		// Miscellaneous glyphs, UTF-8
		for($i=0;$i<$dataNum;$i++){
			for($j=0;$j<$cellNum;$j++){
				$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
			}
		}

        // for($i=0;$i<$cellNum;$i++){
        //     $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.($i+2), $expCellName[$i][1]);
        // }
        // for($i=0;$i<$dataNum;$i++){
        //     for($j=0;$j<$cellNum;$j++){
        //         $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$i+1].($j+2), $expTableData[$i][$expCellName[$j][0]]);
        //         $objPHPExcel->getActiveSheet()->getColumnDimension($cellName[$i+1])->setWidth(5);
        //     }
        // }

        // for($i=0;$i<$cellNum;$i++){
        //     $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.($i+11), $expCellName[$i][1]);
        // }
        // for($i=0;$i<$dataNum;$i++){
        //     for($j=0;$j<$cellNum;$j++){
        //         $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$i+1].($j+11), $expTableData[$i][$expCellName[$j][0]]);
        //     }
        // }
		header('pragma:public');
		header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
		header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
		$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
		exit;
	}

    /**
     *
     * 导出Excel 捕鼠盒
     */
    public function User(){//导出Excel
        $cate_id = C('DEV_LOG_2');
        $xlsName  = M('device_cate')->where(array('id'=>$cate_id))->getField('cate_name');
        $shop_id = I('get.shop_id')?I('get.shop_id'):1;
        $shop_name = M('shop')->where('id='.$shop_id)->getField('shop_name');

        $end_time   = I('get.end_time') ? strtotime(I('get.end_time')) : time();

        $xlsCell  = array(
            array('id','编号'),
            array('yue','月份'),
            array('yue1','1'),
            array('yue2','2'),
            array('yue3','3'),
            array('yue4','4'),
            array('yue5','5'),
            array('yue6','6'),
            array('yue7','7'),
            array('yue8','8'),
            array('yue9','9'),
            array('yue10','10'),
            array('yue11','11'),
            array('yue12','12'),
        );

        // $animal_id = M('device_cate')->where('id='.$cate_id)->getField('pest');

        $data = M('device a')
            ->where(array('a.shop_id'=>$shop_id))
            ->where('a.cate_id='.$cate_id)
            ->field('a.id')
            ->select();

        // $baitModel = M('bait_log');
        // foreach ($data as $k => $v) {
        //     $xlsData[$k]['yue'] = $v['id'];
        //     $xlsData[$k]['yue1'] = $baitModel->where('date_m=1 AND device_id='.$v['id'])->getField('number');
        //     $xlsData[$k]['yue2'] = $baitModel->where('date_m=2 AND device_id='.$v['id'])->getField('number');
        //     $xlsData[$k]['yue3'] = $baitModel->where('date_m=3 AND device_id='.$v['id'])->getField('number');
        //     $xlsData[$k]['yue4'] = $baitModel->where('date_m=4 AND device_id='.$v['id'])->getField('number');
        //     $xlsData[$k]['yue5'] = $baitModel->where('date_m=5 AND device_id='.$v['id'])->getField('number');
        //     $xlsData[$k]['yue6'] = $baitModel->where('date_m=6 AND device_id='.$v['id'])->getField('number');
        //     $xlsData[$k]['yue7'] = $baitModel->where('date_m=7 AND device_id='.$v['id'])->getField('number');
        //     $xlsData[$k]['yue8'] = $baitModel->where('date_m=8 AND device_id='.$v['id'])->getField('number');
        //     $xlsData[$k]['yue9'] = $baitModel->where('date_m=9 AND device_id='.$v['id'])->getField('number');
        //     $xlsData[$k]['yue10'] = $baitModel->where('date_m=10 AND device_id='.$v['id'])->getField('number');
        //     $xlsData[$k]['yue11'] = $baitModel->where('date_m=11 AND device_id='.$v['id'])->getField('number');
        //     $xlsData[$k]['yue12'] = $baitModel->where('date_m=12 AND device_id='.$v['id'])->getField('number');
        // }
        //循环创造出所有月份的数据
        foreach ($data as $key => $value) {
            $ids[$key] = $value['id'];
            for($i=1;$i<13;$i++){
                $data[$key]["yue".$i] = 0;
            }
        }
        //查找数据
        $where = array();
        //数据的设备id为上面得到的id
        $where['device_id'] = array('in',$ids);
        //查找今年的
        $where['date_y'] =intval(date("Y"));

        $zong = M('yue_zong')->where($where)->where(array('date_m'=>intval(date('m',$end_time))))->getField('device_id,content');

        $zongids = array_keys($zong);
        foreach ($ids as $key => $value) 
        {
            $content[] = in_array($value, $zongids) ? $zong[$value] : '本月正常' ;
        }

        $list = M('bait_log a')
              ->field("a.id,a.device_id,a.number,a.date_m,a.shop_id")
              ->where($where)
              ->select();
        //拼装数据
        foreach ($data as $key => $value) {
            $data[$key]["yue"]='盗食量';
            foreach ($list as $k => $v) {
                if($value['id'] == $v['device_id'])
                    $data[$key]["yue".$v['date_m']] += $v['number'];
            }
        }
        // dump($xlsData);exit();
        $name = array(
            'filename'=>$shop_name.$xlsName.'图表',
            'title'=>'食量分析',
            'content'=>$content,
            'zongjie'=>M('zong')->where(array('date_y'=>intval(date("Y")),'dev_cate_id'=>$cate_id,'shop_id'=>$shop_id,'date_m'=>intval(date('m',$end_time))))->order('id desc')->getField('content'),
            );

        $this->UserBt($xlsName,$xlsCell,$data,$name);
        // $this->exportExcel($xlsName,$xlsCell,$xlsData);
    }

    // 飞虫
    public function Insect(){

        $cate_id = C('DEV_LOG_1');
        $xlsName  = M('device_cate')->where(array('id'=>$cate_id))->getField('cate_name');// 二维码分类名称
        $shop_id = I('get.shop_id')?I('get.shop_id'):1;
        $shop_name = M('shop')->where('id='.$shop_id)->getField('shop_name');//工厂名称

        $xlsCell  = array(
            array('yue','CL编号'),
            array('area','地点'),
            array('feie','苍蝇'),
            array('cangy','蚊子'),
            array('wenzi','蛾蒙'),
            array('other','其他'),
            array('sum','飞虫总数'),
            array('insect','飞虫总指数'),
            array('safe','安全指数'),
        );

        $begin_time = I('get.begin_time') ? strtotime(I('get.begin_time')) : $time;
        $end_time   = I('get.end_time') ? strtotime(I('get.end_time')) : time();
        $timediff = $end_time-$begin_time;
        $days = intval($timediff/86400);// 相隔多少天用来计算指数

        $maps['add_time'] = array(array('gt',$begin_time),array('lt',$end_time));
        $maps['shop_id'] = $shop_id;
        $cate_id = C('DEV_LOG_1');
        $animal_id = M('device_cate')->where('id='.$cate_id)->getField('pest');

        $map['id']=array('in',$animal_id);
        $animal_name=M('animal')->where($map)->field('id,name,ename')->select();

        $data = M('device a')
            ->where('a.cate_id='.$cate_id)
            ->where(array('a.shop_id'=>$shop_id))
            ->field('a.id,a.name,area')
            ->select();

        foreach ($data as $k => $v) {

            foreach ($animal_name as $key => $val) {
                $data[$k][$val['ename']] = M('feichong_log')
                    ->where($maps)
                    ->where('device_id='.$v['id'].' AND animal_id='.$val['id'])
                    ->getField('sum(number) as sum_number');

                $data[$k]['sum'] += $data[$k][$val['ename']];
            }
            
        }

        $safe_num_fei = M('configs')->where("ename='safe_num_fei'")->getField('val');
        foreach ($data as $k => $v) {
            $data[$k]['insect'] = sprintf('%.2f',$v['sum']/$days);//飞虫总指数
            $data[$k]['safe'] = (int)$safe_num_fei;
        }

        foreach ($data as $k => $v) {
            $xlsData[$k]['yue']  = $v['id'];
            $xlsData[$k]['area'] = $v['area'];
            $xlsData[$k]['feie'] = $v['feie'];
            $xlsData[$k]['cangy'] = $v['cangy'];
            $xlsData[$k]['wenzi'] = $v['wenzi'];
            $xlsData[$k]['other'] = $v['other'];
            $xlsData[$k]['sum'] = $v['sum'];
            $xlsData[$k]['insect'] = $v['insect'];
            $xlsData[$k]['safe'] = $v['safe'];
        }
        // $title  =array(
        //     array(
        //     'yue'=>' ',
        //     'area'=>' ',
        //     'feie'=>' ',
        //     'cangy'=>' ',
        //     'wenzi'=>' ',
        //     'other'=>' ',
        //     'sum'=>' ',
        //     'insect'=>' ',
        //     'safe'=>' ',
        //     ), 
        //     array(
        //     'yue'=>'CL编号',
        //     'area'=>'地点',
        //     'feie'=>'苍蝇',
        //     'cangy'=>'蚊子',
        //     'wenzi'=>'蛾蒙',
        //     'other'=>'其他',
        //     'sum'=>'飞虫总数',
        //     'insect'=>'飞虫总指数',
        //     'safe'=>'安全指数',
        //     ),
        // );
        // $datas = array_merge($title,$xlsData);
        // dump($datas);exit();
        $content = M('zong')->where(array('date_y'=>intval(date("Y")),'dev_cate_id'=>$cate_id,'shop_id'=>$shop_id,'date_m'=>intval(date('m',$end_time))))->order('id desc')->getField('content');

        $name = array(
            'filename'=> $shop_name.$xlsName.'图表', //工程名称+二维码名称
            'title'   => $xlsName.'图表', // 图表标题
            'content' => $content, // 总结
            );

        $this->InsectBt($xlsName,$xlsCell,$xlsData,$name);

        // $this->exportExcel($xlsName,$xlsCell,$xlsData);

        // $this->InsectBt2($datas,$content);
    }

    // 飞虫 月度表
    public function Insects(){
        $cate_id = C('DEV_LOG_1');
        $shop_id = I('get.shop_id')?I('get.shop_id'):1;
        $shop_name = M('shop')->where('id='.$shop_id)->getField('shop_name');
        $xlsName  = M('device_cate')->where(array('id'=>$cate_id))->getField('cate_name');

        $end_time   = I('get.end_time') ? strtotime(I('get.end_time')) : time();

        $xlsCell  = array(
            array('id','编号'),
            array('yue','月份'),
            array('yue1','1'),
            array('yue2','2'),
            array('yue3','3'),
            array('yue4','4'),
            array('yue5','5'),
            array('yue6','6'),
            array('yue7','7'),
            array('yue8','8'),
            array('yue9','9'),
            array('yue10','10'),
            array('yue11','11'),
            array('yue12','12'),
        );

        // $animal_id = M('device_cate')->where('id='.$cate_id)->getField('pest');
        $map['a.cate_id']=$cate_id;
        $map['a.shop_id']=$shop_id;
        $data = M('device a')
            ->where($map)
            ->field('a.id')
            ->select();
        // $MODEL = M('feichong_log');

        //循环创造出所有月份的数据
        foreach ($data as $key => $value) {
            $ids[$key] = $value['id'];
            for($i=1;$i<13;$i++){
                $data[$key]["yue".$i] = 0;
            }
        }
        //查找数据
        $where = array();
        //数据的设备id为上面得到的id
        $where['device_id'] = array('in',$ids);
        //查找今年的
        $where['date_y'] =intval(date("Y"));

        $zong = M('yue_zong')->where($where)->where(array('date_m'=>intval(date('m',$end_time))))->getField('device_id,content');

        $zongids = array_keys($zong);
        foreach ($ids as $key => $value) 
        {
            $content[] = in_array($value, $zongids) ? $zong[$value] : '本月正常' ;
        }
        $list = M('feichong_log a')
              ->field("a.id,a.device_id,a.number,a.date_m,a.shop_id")
              ->where($where)
              ->select();
        //拼装数据
        foreach ($data as $key => $value) {
            $data[$key]["yue"]='盗食量';
            foreach ($list as $k => $v) {
                if($value['id'] == $v['device_id'])
                    $data[$key]["yue".$v['date_m']] += $v['number'];
            }
        }

        $name = array(
            'filename'=>$shop_name.$xlsName.'图表',
            'title'=>'飞虫灯月记录',
            'content'=>$content,
            'zongjie'=>M('zong')->where(array('date_y'=>intval(date("Y")),'dev_cate_id'=>$cate_id,'shop_id'=>$shop_id,'date_m'=>intval(date('m',$end_time))))->order('id desc')->getField('content'),
            );
        $this->UserBt($xlsName,$xlsCell,$data,$name);
    }

    // 爬虫
    public function MouseRep(){

        $cate_id = C('DEV_LOG_5');
        $xlsName  = M('device_cate')->where(array('id'=>$cate_id))->getField('cate_name');// 二维码分类名称
        $shop_id = I('get.shop_id')?I('get.shop_id'):1;
        $shop_name = M('shop')->where('id='.$shop_id)->getField('shop_name');//工厂名称

        $xlsCell  = array(
            array('yue','CL编号'),
            array('area','地点'),
            array('zhanglang','蜚蠊'),
            array('zhizhu','蜘蛛'),
            array('bihu','壁虎'),
            array('other','其他'),
            array('sum','爬虫总数'),
            array('insect','爬虫总指数'),
            array('safe','安全指数'),
        );

        $begin_time = I('get.begin_time') ? strtotime(I('get.begin_time')) : $time;
        $end_time   = I('get.end_time') ? strtotime(I('get.end_time')) : time();
        $timediff = $end_time-$begin_time;
        $days = intval($timediff/86400);// 相隔多少天用来计算指数

        $maps['add_time'] = array(array('gt',$begin_time),array('lt',$end_time));
        $maps['shop_id'] = $shop_id;

        $cate_id = C('DEV_LOG_5');
        $animal_id = M('device_cate')->where('id='.$cate_id)->getField('pest');

        $map['id']=array('in',$animal_id);
        $animal_name=M('animal')->where($map)->field('id,name,ename')->select();


        $data = M('device a')
            ->where('a.cate_id='.$cate_id)
            ->where(array('a.shop_id'=>$shop_id))
            ->field('a.id,a.name,area')
            ->select();
        
        $MODEL = M('reptile_log');

        foreach ($data as $k => $v) {

            foreach ($animal_name as $key => $val) {
                $data[$k][$val['ename']] = $MODEL
                    ->where($maps)
                    ->where('device_id='.$v['id'].' AND animal_id='.$val['id'])
                    ->getField('sum(number) as sum_number');

                $data[$k]['sum'] += $data[$k][$val['ename']];
            }
            
        }

        $safe_num_fei = M('configs')->where("ename='safe_num_pa'")->getField('val');
        foreach ($data as $k => $v) {
            $data[$k]['insect'] = sprintf('%.2f',$v['sum']/$days);//爬虫总指数
            $data[$k]['safe'] = (int)$safe_num_fei;
        }

        foreach ($data as $k => $v) {
            $xlsData[$k]['yue']  = $v['id'];
            $xlsData[$k]['area'] = $v['area'];
            $xlsData[$k]['zhanglang'] = $v['zhanglang'];
            $xlsData[$k]['zhizhu'] = $v['zhizhu'];
            $xlsData[$k]['bihu'] = $v['bihu'];
            $xlsData[$k]['other'] = $v['other'];
            $xlsData[$k]['sum'] = $v['sum'];
            $xlsData[$k]['insect'] = $v['insect'];
            $xlsData[$k]['safe'] = $v['safe'];
        }
        // $this->exportExcel($xlsName,$xlsCell,$xlsData);
        $content = M('zong')->where(array('date_y'=>intval(date("Y")),'dev_cate_id'=>$cate_id,'shop_id'=>$shop_id,'date_m'=>intval(date('m',$end_time))))->order('id desc')->getField('content');

        $name = array(
            'filename'=> $shop_name.$xlsName.'图表', //工程名称+二维码名称
            'title'   => $xlsName.'图表', // 图表标题
            'content' => $content, // 总结
            );

        $this->InsectBt($xlsName,$xlsCell,$xlsData,$name);
    }

    // 爬虫 月度表
    public function MouseReps(){
        $cate_id = C('DEV_LOG_5');
        $shop_id = I('get.shop_id')?I('get.shop_id'):1;
        $shop_name = M('shop')->where('id='.$shop_id)->getField('shop_name');
        $xlsName  = M('device_cate')->where(array('id'=>$cate_id))->getField('cate_name');

        $end_time   = I('get.end_time') ? strtotime(I('get.end_time')) : time();
        $xlsCell  = array(
            array('id','编号'),
            array('yue','月份'),
            array('yue1','1'),
            array('yue2','2'),
            array('yue3','3'),
            array('yue4','4'),
            array('yue5','5'),
            array('yue6','6'),
            array('yue7','7'),
            array('yue8','8'),
            array('yue9','9'),
            array('yue10','10'),
            array('yue11','11'),
            array('yue12','12'),
        );

        // $animal_id = M('device_cate')->where('id='.$cate_id)->getField('pest');
        $map['cate_id']=$cate_id;
        $map['shop_id']=$shop_id;
        $data = M('device')
            ->where($map)
            ->field('id')
            ->select();

        // $MODEL = M('reptile_log');

        //循环创造出所有月份的数据
        foreach ($data as $key => $value) {
            $ids[$key] = $value['id'];
            for($i=1;$i<13;$i++){
                $data[$key]["yue".$i] = 0;
            }
        }
        //查找数据
        $where = array();
        //数据的设备id为上面得到的id
        $where['device_id'] = array('in',$ids);
        //查找今年的
        $where['date_y'] =intval(date("Y"));

        $zong = M('yue_zong')->where($where)->where(array('date_m'=>intval(date('m',$end_time))))->getField('device_id,content');

        $zongids = array_keys($zong);
        foreach ($ids as $key => $value) 
        {
            $content[] = in_array($value, $zongids) ? $zong[$value] : '本月正常' ;
        }
        $list = M('reptile_log a')
              ->field("a.id,a.device_id,a.number,a.date_m,a.shop_id")
              ->where($where)
              ->select();
        //拼装数据
        foreach ($data as $key => $value) {
            $data[$key]["yue"]='盗食量';
            foreach ($list as $k => $v) {
                if($value['id'] == $v['device_id'])
                    $data[$key]["yue".$v['date_m']] += $v['number'];
            }
        }
        // dump($xlsData);exit();
        $name = array(
            'filename'=>$shop_name.$xlsName.'图表',
            'title'=>'飞虫灯月记录',
            'content'=>$content,
            'zongjie'=>M('zong')->where(array('date_y'=>intval(date("Y")),'dev_cate_id'=>$cate_id,'shop_id'=>$shop_id,'date_m'=>intval(date('m',$end_time))))->order('id desc')->getField('content'),
            );
        $this->UserBt($xlsName,$xlsCell,$data,$name);
    }

    public function MousePlate1(){
        $xlsName  = "粘鼠板";
        $xlsCell  = array(
            array('yue','CL编号'),
            array('area','地点'),
            array('smallmouse','小家鼠'),
            array('hemouse','褐家鼠'),
            array('humouse','黄胸鼠'),
            array('other','其他'),
            array('sum','总数'),
        );
        $begin_time = I('get.begin_time') ? strtotime(I('get.begin_time')) : $time;
        $end_time   = I('get.end_time') ? strtotime(I('get.end_time')) : time();
        $timediff = $end_time-$begin_time;
        $days = intval($timediff/86400);// 相隔多少天用来计算指数

        $maps['add_time'] = array(array('gt',$begin_time),array('lt',$end_time));

        $cate_id = C('DEV_LOG_3');
        $animal_id = M('device_cate')->where('id='.$cate_id)->getField('pest');

        $map['id']=array('in',$animal_id);
        $animal_name=M('animal')->where($map)->field('id,name,ename')->select();
        // dump($animal_name);exit();
        $data = M('device a')
            ->where('a.cate_id='.$cate_id)
            ->field('a.id,a.name,area')
            ->select();

        foreach ($data as $k => $v) {
            // $data[$k]['feichong_log'] = M('feichong_log')->where('device_id='.$v['id'])->select();

            foreach ($animal_name as $key => $val) {
                $data[$k][$val['ename']] = M('glue_log')
                    ->where($maps)
                    ->where('device_id='.$v['id'].' AND animal_id='.$val['id'])
                    ->getField('sum(number) as sum_number');

                $data[$k]['sum'] += $data[$k][$val['ename']];
            }
            
        }
        foreach ($data as $k => $v) {
            $xlsData[$k]['yue']  = $v['id'];
            $xlsData[$k]['area'] = $v['area'];
            $xlsData[$k]['smallmouse'] = $v['smallmouse'];
            $xlsData[$k]['hemouse'] = $v['hemouse'];
            $xlsData[$k]['humouse'] = $v['humouse'];
            $xlsData[$k]['other'] = $v['other'];
            $xlsData[$k]['sum'] = $v['sum'];
        }
        $this->exportExcel($xlsName,$xlsCell,$xlsData);
    }
    
    //粘鼠板
    public function MousePlate(){
        $cate_id = C('DEV_LOG_3');
        $shop_id = I('get.shop_id')?I('get.shop_id'):1;
        $shop_name = M('shop')->where('id='.$shop_id)->getField('shop_name');
        $xlsName  = M('device_cate')->where(array('id'=>$cate_id))->getField('cate_name');

        $time = strtotime(date('Y-m-d',strtotime('-30 day')));
        $begin_time = I('get.begin_time') ? strtotime(I('get.begin_time')) : $time;
        $end_time   = I('get.end_time') ? strtotime(I('get.end_time')) : time();
        // $timediff = $end_time-$begin_time;
        // $days = intval($timediff/86400);// 相隔多少天用来计算指数

        $maps['add_time'] = array(array('gt',$begin_time),array('lt',$end_time));
        $maps['shop_id'] = $shop_id;

        $data = M('device a')
            ->where('a.cate_id='.$cate_id)
            ->where(array('a.shop_id'=>$shop_id))
            ->field('a.id,a.name,a.area')
            ->select();

        $MODEL = M('glue_log');
        foreach ($data as $k => $v) {
            $list[$k]['device_id'] = $v['id'];
            $list[$k]['number'] = $MODEL->where('device_id='.$v['id'])->where($maps)->getField('sum(number)');
            $list[$k]['number'] = $list[$k]['number']?$list[$k]['number']:0;
        }
        foreach ($list as $k => $v) {
            $xlsData[$k]['device_id'] = $v['device_id'];
            $xlsData[$k]['number'] = $v['number'];
        }
        $xlsCell  = array(
            array('device_id','编号'),
            array('number','数量'),
        );
        // dump($list);exit();
        $content = M('zong')->where(array('date_y'=>intval(date("Y")),'dev_cate_id'=>$cate_id,'shop_id'=>$shop_id,'date_m'=>intval(date('m',$end_time))))->order('id desc')->getField('content');
        $name = array(
            'filename'=> $shop_name.$xlsName.'图表', //工程名称+二维码名称
            'title'   => $xlsName.'图表', // 图表标题
            'content' => $content, // 总结
            );

        $this->MouseBt($xlsName,$xlsCell,$xlsData,$name);
    }

    public function MousePlates(){
        $cate_id = C('DEV_LOG_3');
        $shop_id = I('get.shop_id')?I('get.shop_id'):1;
        $shop_name = M('shop')->where('id='.$shop_id)->getField('shop_name');
        $xlsName  = M('device_cate')->where(array('id'=>$cate_id))->getField('cate_name');

        $end_time   = I('get.end_time') ? strtotime(I('get.end_time')) : time();
        $xlsCell  = array(
            array('id','编号'),
            array('yue','月份'),
            array('yue1','1'),
            array('yue2','2'),
            array('yue3','3'),
            array('yue4','4'),
            array('yue5','5'),
            array('yue6','6'),
            array('yue7','7'),
            array('yue8','8'),
            array('yue9','9'),
            array('yue10','10'),
            array('yue11','11'),
            array('yue12','12'),
        );

        // $animal_id = M('device_cate')->where('id='.$cate_id)->getField('pest');
        $map['cate_id']=$cate_id;
        $map['shop_id']=$shop_id;
        $data = M('device')
            ->where($map)
            ->field('id')
            ->select();

        // $MODEL = M('glue_log');

        //循环创造出所有月份的数据
        foreach ($data as $key => $value) {
            $ids[$key] = $value['id'];
            for($i=1;$i<13;$i++){
                $data[$key]["yue".$i] = 0;
            }
        }
        //查找数据
        $where = array();
        //数据的设备id为上面得到的id
        $where['device_id'] = array('in',$ids);
        //查找今年的
        $where['date_y'] =intval(date("Y"));

        $zong = M('yue_zong')->where($where)->where(array('date_m'=>intval(date('m',$end_time))))->getField('device_id,content');

        $zongids = array_keys($zong);
        foreach ($ids as $key => $value) 
        {
            $content[] = in_array($value, $zongids) ? $zong[$value] : '本月正常' ;
        }
        $list = M('glue_log a')
              ->field("a.id,a.device_id,a.number,a.date_m,a.shop_id")
              ->where($where)
              ->select();
        //拼装数据
        foreach ($data as $key => $value) {
            $data[$key]["yue"]='盗食量';
            foreach ($list as $k => $v) {
                if($value['id'] == $v['device_id'])
                    $data[$key]["yue".$v['date_m']] += $v['number'];
            }
        }

        // dump($data);exit();
        $name = array(
            'filename'=>$shop_name.$xlsName.'图表',
            'title'=>'鼠板记录',
            'content'=>$content,
            'zongjie'=>M('zong')->where(array('date_y'=>intval(date("Y")),'dev_cate_id'=>$cate_id,'shop_id'=>$shop_id,'date_m'=>intval(date('m',$end_time))))->order('id desc')->getField('content'),
            );
        $this->UserBt($xlsName,$xlsCell,$data,$name);
    }
    //
    public function MouseIron1(){

        $xlsName  = "机械捕鼠器";
        $xlsCell  = array(
            array('yue','CL编号'),
            array('area','地点'),
            array('smallmouse','小家鼠'),
            array('hemouse','褐家鼠'),
            array('humouse','黄胸鼠'),
            array('other','其他'),
            array('sum','总数'),
        );

        $begin_time = I('get.begin_time') ? strtotime(I('get.begin_time')) : $time;
        $end_time   = I('get.end_time') ? strtotime(I('get.end_time')) : time();
        $timediff = $end_time-$begin_time;
        $days = intval($timediff/86400);// 相隔多少天用来计算指数

        $maps['add_time'] = array(array('gt',$begin_time),array('lt',$end_time));

        $cate_id = C('DEV_LOG_4');
        $animal_id = M('device_cate')->where('id='.$cate_id)->getField('pest');

        $map['id']=array('in',$animal_id);
        $animal_name=M('animal')->where($map)->field('id,name,ename')->select();
        // dump($animal_name);exit();
        $data = M('device a')
            ->where('a.cate_id='.$cate_id)
            ->field('a.id,a.name,area')
            ->select();

        foreach ($data as $k => $v) {
            // $data[$k]['feichong_log'] = M('feichong_log')->where('device_id='.$v['id'])->select();

            foreach ($animal_name as $key => $val) {
                $data[$k][$val['ename']] = M('mousetrap_log')
                    ->where($maps)
                    ->where('device_id='.$v['id'].' AND animal_id='.$val['id'])
                    ->getField('sum(number) as sum_number');

                $data[$k]['sum'] += $data[$k][$val['ename']];
            }
            
        }


        foreach ($data as $k => $v) {
            $xlsData[$k]['yue']  = $v['id'];
            $xlsData[$k]['area'] = $v['area'];
            $xlsData[$k]['smallmouse'] = $v['smallmouse'];
            $xlsData[$k]['hemouse'] = $v['hemouse'];
            $xlsData[$k]['humouse'] = $v['humouse'];
            $xlsData[$k]['other'] = $v['other'];
            $xlsData[$k]['sum'] = $v['sum'];
        }
        $this->exportExcel($xlsName,$xlsCell,$xlsData);
    }
    // 捕鼠夹
    public function MouseIron(){
        $cate_id = C('DEV_LOG_4');
        $shop_id = I('get.shop_id')?I('get.shop_id'):1;
        $shop_name = M('shop')->where('id='.$shop_id)->getField('shop_name');
        $xlsName  = M('device_cate')->where(array('id'=>$cate_id))->getField('cate_name');

        $time = strtotime(date('Y-m-d',strtotime('-30 day')));
        $begin_time = I('get.begin_time') ? strtotime(I('get.begin_time')) : $time;
        $end_time   = I('get.end_time') ? strtotime(I('get.end_time')) : time();
        // $timediff = $end_time-$begin_time;
        // $days = intval($timediff/86400);// 相隔多少天用来计算指数

        $maps['add_time'] = array(array('gt',$begin_time),array('lt',$end_time));
        $maps['shop_id'] = $shop_id;

        $data = M('device a')
            ->where('a.cate_id='.$cate_id)
            ->where(array('a.shop_id'=>$shop_id))
            ->field('a.id,a.name,a.area')
            ->select();

        $MODEL = M('mousetrap_log');
        foreach ($data as $k => $v) {
            $list[$k]['device_id'] = $v['id'];
            $list[$k]['number'] = $MODEL->where('device_id='.$v['id'])->where($maps)->getField('sum(number)');
            $list[$k]['number'] = $list[$k]['number']?$list[$k]['number']:0;
        }
        foreach ($list as $k => $v) {
            $xlsData[$k]['device_id'] = $v['device_id'];
            $xlsData[$k]['number'] = $v['number'];
        }
        $xlsCell  = array(
            array('device_id','编号'),
            array('number','数量'),
        );
        // dump($list);exit();
        $content = M('zong')->where(array('date_y'=>intval(date("Y")),'dev_cate_id'=>$cate_id,'shop_id'=>$shop_id,'date_m'=>intval(date('m',$end_time))))->order('id desc')->getField('content');
        $name = array(
            'filename'=> $shop_name.$xlsName.'图表', //工程名称+二维码名称
            'title'   => $xlsName.'图表', // 图表标题
            'content' => $content, // 总结
            );

        $this->MouseBt($xlsName,$xlsCell,$xlsData,$name);
    }

    public function MouseIrons(){
        $cate_id = C('DEV_LOG_4');
        $shop_id = I('get.shop_id')?I('get.shop_id'):1;
        $shop_name = M('shop')->where('id='.$shop_id)->getField('shop_name');
        $xlsName  = M('device_cate')->where(array('id'=>$cate_id))->getField('cate_name');

        $end_time   = I('get.end_time') ? strtotime(I('get.end_time')) : time();
        $xlsCell  = array(
            array('id','编号'),
            array('yue','月份'),
            array('yue1','1'),
            array('yue2','2'),
            array('yue3','3'),
            array('yue4','4'),
            array('yue5','5'),
            array('yue6','6'),
            array('yue7','7'),
            array('yue8','8'),
            array('yue9','9'),
            array('yue10','10'),
            array('yue11','11'),
            array('yue12','12'),
        );

        // $animal_id = M('device_cate')->where('id='.$cate_id)->getField('pest');
        $map['cate_id']=$cate_id;
        $map['shop_id']=$shop_id;
        $data = M('device')
            ->where($map)
            ->field('id')
            ->select();

        // $MODEL = M('glue_log');

        //循环创造出所有月份的数据
        foreach ($data as $key => $value) {
            $ids[$key] = $value['id'];
            for($i=1;$i<13;$i++){
                $data[$key]["yue".$i] = 0;
            }
        }
        //查找数据
        $where = array();
        //数据的设备id为上面得到的id
        $where['device_id'] = array('in',$ids);
        //查找今年的
        $where['date_y'] =intval(date("Y"));

        $zong = M('yue_zong')->where($where)->where(array('date_m'=>intval(date('m',$end_time))))->getField('device_id,content');

        $zongids = array_keys($zong);
        foreach ($ids as $key => $value) 
        {
            $content[] = in_array($value, $zongids) ? $zong[$value] : '本月正常' ;
        }
        $list = M('mousetrap_log a')
              ->field("a.id,a.device_id,a.number,a.date_m,a.shop_id")
              ->where($where)
              ->select();
        //拼装数据
        foreach ($data as $key => $value) {
            $data[$key]["yue"]='盗食量';
            foreach ($list as $k => $v) {
                if($value['id'] == $v['device_id'])
                    $data[$key]["yue".$v['date_m']] += $v['number'];
            }
        }

        // dump($data);exit();
        $name = array(
            'filename'=>$shop_name.$xlsName.'图表',
            'title'=>'鼠板记录',
            'content'=>$content,
            'zongjie'=>M('zong')->where(array('date_y'=>intval(date("Y")),'dev_cate_id'=>$cate_id,'shop_id'=>$shop_id,'date_m'=>intval(date('m',$end_time))))->order('id desc')->getField('content'),
            );
        $this->UserBt($xlsName,$xlsCell,$data,$name);
    }


    public function demo(){
        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel(); 
        $objSheet = $objPHPExcel->getActiveSheet();  
      
        $data = array(  
            array("","一班","二班","三班","4班"),  
            array("不及格",20,30,40),  
            array("良好",30,50,70),  
            array("优秀",14,12,30), 

        );  
        $objSheet->fromArray($data);  
      
        //图表  
        $labels = array(  
            new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$B$1',null,1),//一班  
            new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$C$1',null,1),//二班  
        );  
        $xLabels = array(  
            new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$A$2:$A$6',null,5),//取x轴刻度  
        );  
        $datas = array(  
            new \PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$B$2:$B$6',null,5),//取一班数据  
            new \PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$C$2:$C$6',null,5),//取二班数据   
        );  
      
        $series = array(  
            new \PHPExcel_Chart_DataSeries(  
                \PHPExcel_Chart_DataSeries::TYPE_LINECHART,  
                \PHPExcel_Chart_DataSeries::GROUPING_STANDARD,  
                range(0, count($labels)-1),  
                $labels,  
                $xLabels,  
                $datas  
            )  
        ); //图表框架  
      
        $layout=new \PHPExcel_Chart_Layout();  
        $layout->setShowVal(true);  
        $areas = new \PHPExcel_Chart_PlotArea($layout,$series);  
        $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT,$layout,false);  
        $title = new \PHPExcel_Chart_Title("高一学生成绩分布");  
        $ytitle = new \PHPExcel_Chart_Title("人数");  
        $chart = new \PHPExcel_Chart('line_chart',$title,$legend,$areas,true,false,null,$ytitle);  
        $chart->setTopLeftPosition("A7")->setBottomRightPosition("K25"); //图表位置  
      
        $objSheet->addChart($chart);  
      
      
        $excel = 'Excel2007';  
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,$excel);  
        $objWriter->setIncludeCharts(true); //图表必须  
        // $objWriter->save($dir.'/export.xls'); //生成excel文件  
        // browser_export($excel,"browser_chart.xlsx"); //浏览器输出  
      
        // SaveViaTempFile($objWriter);
        $filename = 'ddd.xlsx';  
      
        // function browser_export($type, $filename){  
            if($type == "Excel5"){  
                header('Content-Type: application/vnd.ms-excel'); //excel2003  
            }else{  
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //excel2007  
            }  
            header('Content-Disposition: attachment;filename="'.$filename.'"');  
            header('Cache-Control: max-age=0');
        // }  
      
        /*解决Excel2007不能导出*/  
        // function SaveViaTempFile($objWriter){  
            $filePath = dirname(__FILE__) . rand(0, getrandmax()) . rand(0, getrandmax()) . ".tmp";
            $objWriter->save($filePath);  
            readfile($filePath);  
            unlink($filePath);  
        // } 
    }

    public function InsectBt1($data){
        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel(); 
        $objSheet = $objPHPExcel->getActiveSheet();
        $objSheet->fromArray($data);
        // $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        // $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName['8'].'1');//合并单元格
        // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1','捕虫灯');
        // $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);// 居中  
        // $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
  
        //柱状  
        $labels1 = array(  
            new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$H$2',null,1),//一班  
            // new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$I$2',null,1),//二班 
        );  
        $xLabels = array(  
            new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$A$3:$A$11',null,8),//取x轴刻度  
        );  
        $datas1 = array(  
            new \PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$H$3:$H$11',null,8),//取一班数据  
            // new \PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$I$3:$I$11',null,8),//取二班数据   
        );  
      
        $series1 = array(  
            new \PHPExcel_Chart_DataSeries(  
                \PHPExcel_Chart_DataSeries::TYPE_BARCHART,  
                \PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,  
                range(0, count($labels1)-1),  
                $labels1,  
                null,  
                $datas1  
            )  
        ); //柱状框架  
        // dump($series1);exit();
        //这里有个重要的配置，主要的对第一个图表柱状图绘图方式的设置。（横着展示还是竖着展示）
        $series1->setPlotDirection(\PHPExcel_Chart_DataSeries::DIRECTION_COL);
        //图表  
        $labels = array(  
            new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$H$2',null,1),//一班  
            new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$I$2',null,1),//二班 
        );  
        $xLabels = array(  
            new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$A$3:$A$11',null,8),//取x轴刻度  
        );  
        $datas = array(  
            new \PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$H$3:$H$11',null,8),//取一班数据  
            new \PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$I$3:$I$11',null,8),//取二班数据   
        );  
      
        $series = array(  
            new \PHPExcel_Chart_DataSeries(  
                \PHPExcel_Chart_DataSeries::TYPE_LINECHART,  
                \PHPExcel_Chart_DataSeries::GROUPING_STANDARD,  
                range(0, count($labels)-1),  
                $labels,  
                $xLabels,  
                $datas  
            )  
        ); //图表框架  
        
        $layout=new \PHPExcel_Chart_Layout();  
        $layout->setShowVal(true);  
        $areas = new \PHPExcel_Chart_PlotArea($layout,$series);
        // $areas = new \PHPExcel_Chart_PlotArea($layout,array($series1, $series));  
        $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_RIGHT,$layout,false);  
        $title = new \PHPExcel_Chart_Title("飞虫灯分布");  
        $ytitle = new \PHPExcel_Chart_Title("数量");  
        $chart = new \PHPExcel_Chart('line_chart',$title,$legend,$areas,true,0,null,$ytitle);  
        $chart->setTopLeftPosition("A12")->setBottomRightPosition("K25"); //图表位置  
      
        $objSheet->addChart($chart);  
      
      
        $excel = 'Excel2007';  
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,$excel);  
        $objWriter->setIncludeCharts(true); //图表必须  
        // $objWriter->save($dir.'/export.xls'); //生成excel文件  
        // browser_export($excel,"browser_chart.xlsx"); //浏览器输出  
      
        // SaveViaTempFile($objWriter);
        $filename = 'ddd.xlsx';  
        
        if($type == "Excel5"){  
            header('Content-Type: application/vnd.ms-excel'); //excel2003  
        }else{  
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //excel2007  
        }  
        header('Content-Disposition: attachment;filename="'.$filename.'"');  
        header('Cache-Control: max-age=0');

      
        /*解决Excel2007不能导出*/  
  
        $filePath = dirname(__FILE__) . rand(0, getrandmax()) . rand(0, getrandmax()) . ".tmp";
        $objWriter->save($filePath);  
        readfile($filePath);  
        unlink($filePath);  
    }

    public function InsectBt2($data,$content){
        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $objWorksheet = $objPHPExcel->getActiveSheet();

        $objWorksheet->fromArray($data);

        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName['8'].'1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1','捕虫灯');

        $objPHPExcel->getActiveSheet(0)->mergeCells('A27:'.$cellName['8'].'27');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A27', $content);//写字
        $objPHPExcel->getActiveSheet()->getStyle('A27')->getAlignment()->setWrapText(true);//自动换行

        $objPHPExcel->getActiveSheet()->getRowDimension('27')->setRowHeight(75);//设置某一行的高度
        
        // 设置为文本
        $objPHPExcel->getActiveSheet()->getStyle('27')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);// 居中  
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);// 单元格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12);
        //  Set the Labels for each data series we want to plot

        $dataseriesLabels1 = array(
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$C$2', NULL, 1),   //  柱状
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$D$2', NULL, 1),   //  柱状
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$E$2', NULL, 1),   //  柱状
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$F$2', NULL, 1),   //  柱状
        );
        $dataseriesLabels2 = array(
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$H$2', NULL, 1),   //  线图
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$I$2', NULL, 1),   //  线图
        );

        //  Set the X-Axis Labels

        $xAxisTickValues = array(
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$3:$A$12', NULL, 10),    //  Jan to Dec
        );


        //  Set the Data values for each data series we want to plot

        $dataSeriesValues1 = array(
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$C$3:$C$12', NULL, 10),
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$D$3:$D$12', NULL, 10),
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$E$3:$E$12', NULL, 10),
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$F$3:$F$12', NULL, 10),
        );

        //  Build the dataseries
        $series1 = new \PHPExcel_Chart_DataSeries(
            \PHPExcel_Chart_DataSeries::TYPE_BARCHART,       // plotType
            \PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,  // plotGrouping
            range(0, count($dataSeriesValues1)-1),          // plotOrder
            $dataseriesLabels1,                             // plotLabel
            $xAxisTickValues,                               // plotCategory
            $dataSeriesValues1                              // plotValues
        );
        //  Set additional dataseries parameters
        //      Make it a vertical column rather than a horizontal bar graph
        $series1->setPlotDirection(\PHPExcel_Chart_DataSeries::DIRECTION_COL);


        //  Set the Data values for each data series we want to plot

        $dataSeriesValues2 = array(
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$H$3:$H$12', NULL, 10),
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$I$3:$I$12', NULL, 10),
        );

        //  Build the dataseries
        $series2 = new \PHPExcel_Chart_DataSeries(
            \PHPExcel_Chart_DataSeries::TYPE_LINECHART,      // plotType
            \PHPExcel_Chart_DataSeries::GROUPING_STANDARD,   // plotGrouping
            range(0, count($dataSeriesValues2)-1),          // plotOrder
            $dataseriesLabels2,                             // plotLabel
            NULL,                                           // plotCategory
            $dataSeriesValues2                              // plotValues
        );

        $layout=new \PHPExcel_Chart_Layout();  
        $layout->setShowVal(true);
        //  Set the series in the plot area
        $plotarea = new \PHPExcel_Chart_PlotArea(null, array($series1, $series2));
        //  Set the chart legend
        $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_BOTTOM, $layout, false);

        $title = new \PHPExcel_Chart_Title('飞虫灯分布');

        $ytitle = new \PHPExcel_Chart_Title("数量");

        //  Create the chart
        $chart = new \PHPExcel_Chart(
            'chart1',       // name
            $title,         // title
            $legend,        // legend
            $plotarea,      // plotArea
            true,           // plotVisibleOnly
            0,              // displayBlanksAs
            NULL,           // xAxisLabel
            $ytitle         // yAxisLabel
        );

        //  Set the position where the chart should appear in the worksheet
        $chart->setTopLeftPosition("A13")->setBottomRightPosition("K25"); //图表位置 
        //  Add the chart to the worksheet
        $objWorksheet->addChart($chart);

        $excel = 'Excel2007';  
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,$excel);  
        $objWriter->setIncludeCharts(true); //图表必须  

        $filename = 'ddd.xlsx';  
      
        if($type == "Excel5"){  
            header('Content-Type: application/vnd.ms-excel'); //excel2003  
        }else{  
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //excel2007  
        }  
        header('Content-Disposition: attachment;filename="'.$filename.'"');  
        header('Cache-Control: max-age=0');
      
        /*解决Excel2007不能导出*/   
        $filePath = dirname(__FILE__) . rand(0, getrandmax()) . rand(0, getrandmax()) . ".tmp";
        $objWriter->save($filePath);  
        readfile($filePath);  
        unlink($filePath);  
    }

    // 混合图
    public function InsectBt($expTitle,$expCellName,$expTableData,$name){
        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $objWorksheet = $objPHPExcel->getActiveSheet();

        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $xlsTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        // $objWorksheet->fromArray($data);

        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName['25'].'1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1',$expTitle);

        $objPHPExcel->getActiveSheet()->getPageMargins()->setTop(0); 
        $objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0); 
        $objPHPExcel->getActiveSheet()->getPageMargins()->setBottom(0);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setHeader(0);
        $objPHPExcel->getActiveSheet()->getPageMargins()->setFooter(0);

        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.($i+2), $expCellName[$i][1]);
        }
        for($i=0;$i<25;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$i+1].($j+2), $expTableData[$i][$expCellName[$j][0]]);
                $objPHPExcel->getActiveSheet()->getColumnDimension($cellName[$i+1])->setWidth(3.5);
            }
        }

        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.($i+11), $expCellName[$i][1]);
        }
        for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$i+1].($j+11), $expTableData[$i+25][$expCellName[$j][0]]);
            }
        }
        $objPHPExcel->getActiveSheet(0)->mergeCells('A41:'.$cellName['25'].'45');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A41', $name['content']);//写字
        $objPHPExcel->getActiveSheet()->getStyle('A41')->getAlignment()->setWrapText(true);//自动换行
        $objPHPExcel->getActiveSheet()->getStyle('A41')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);//对齐
        // $objPHPExcel->getActiveSheet()->getRowDimension('41')->setRowHeight(75);//设置某一行的高度
        
        // 设置为文本
        // $objPHPExcel->getActiveSheet()->getStyle('27')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);// 居中  
        // $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);// 单元格宽度
        // $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
        // $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);
        // $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12);
        //  Set the Labels for each data series we want to plot

        $dataseriesLabels1 = array(
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$4', NULL, 1),   //  柱状
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$5', NULL, 1),   //  柱状
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$6', NULL, 1),   //  柱状
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$7', NULL, 1),   //  柱状
        );
        $dataseriesLabels2 = array(
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$9', NULL, 1),   //  线图
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$10', NULL, 1),   //  线图
        );

        //  Set the X-Axis Labels

        $xAxisTickValues = array(
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$2:$S$2', NULL, 19),    //  Jan to Dec
        );


        //  Set the Data values for each data series we want to plot

        $dataSeriesValues1 = array(
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$4:$S$4', NULL, 19),
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$5:$S$5', NULL, 19),
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$6:$S$6', NULL, 19),
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$7:$S$7', NULL, 19),
        );

        //  Build the dataseries
        $series1 = new \PHPExcel_Chart_DataSeries(
            \PHPExcel_Chart_DataSeries::TYPE_BARCHART,       // plotType
            \PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,  // plotGrouping
            range(0, count($dataSeriesValues1)-1),          // plotOrder
            $dataseriesLabels1,                             // plotLabel
            $xAxisTickValues,                               // plotCategory
            $dataSeriesValues1                              // plotValues
        );
        //  Set additional dataseries parameters
        //      Make it a vertical column rather than a horizontal bar graph
        $series1->setPlotDirection(\PHPExcel_Chart_DataSeries::DIRECTION_COL);


        //  Set the Data values for each data series we want to plot

        $dataSeriesValues2 = array(
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$9:$S$9', NULL, 19),
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$10:$S$10', NULL, 19),
        );

        //  Build the dataseries
        $series2 = new \PHPExcel_Chart_DataSeries(
            \PHPExcel_Chart_DataSeries::TYPE_LINECHART,      // plotType
            \PHPExcel_Chart_DataSeries::GROUPING_STANDARD,   // plotGrouping
            range(0, count($dataSeriesValues2)-1),          // plotOrder
            $dataseriesLabels2,                             // plotLabel
            NULL,                                           // plotCategory
            $dataSeriesValues2                              // plotValues
        );

        $layout=new \PHPExcel_Chart_Layout();  
        $layout->setShowVal(true);
        //  Set the series in the plot area
        $plotarea = new \PHPExcel_Chart_PlotArea(null, array($series1, $series2));
        //  Set the chart legend
        $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_BOTTOM, $layout, false);

        $title = new \PHPExcel_Chart_Title($name['title']);

        $ytitle = new \PHPExcel_Chart_Title("数量");

        //  Create the chart
        $chart = new \PHPExcel_Chart(
            'chart1',       // name
            $title,         // title
            $legend,        // legend
            $plotarea,      // plotArea
            true,           // plotVisibleOnly
            0,              // displayBlanksAs
            NULL,           // xAxisLabel
            $ytitle         // yAxisLabel
        );

        //  Set the position where the chart should appear in the worksheet
        $chart->setTopLeftPosition("A21")->setBottomRightPosition("Z30"); //图表位置 
        //  Add the chart to the worksheet
        $objWorksheet->addChart($chart);


        //-----------------------------------


        $dataseriesLabels1 = array(
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$13', NULL, 1),   //  柱状
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$14', NULL, 1),   //  柱状
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$15', NULL, 1),   //  柱状
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$16', NULL, 1),   //  柱状
        );
        $dataseriesLabels2 = array(
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$18', NULL, 1),   //  线图
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$A$19', NULL, 1),   //  线图
        );

        //  Set the X-Axis Labels

        $xAxisTickValues = array(
            new \PHPExcel_Chart_DataSeriesValues('String', 'Worksheet!$B$2:$S$2', NULL, 19),    //  Jan to Dec
        );


        //  Set the Data values for each data series we want to plot

        $dataSeriesValues1 = array(
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$13:$S$13', NULL, 19),
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$14:$S$14', NULL, 19),
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$15:$S$15', NULL, 19),
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$16:$S$16', NULL, 19),
        );

        //  Build the dataseries
        $series1 = new \PHPExcel_Chart_DataSeries(
            \PHPExcel_Chart_DataSeries::TYPE_BARCHART,       // plotType
            \PHPExcel_Chart_DataSeries::GROUPING_CLUSTERED,  // plotGrouping
            range(0, count($dataSeriesValues1)-1),          // plotOrder
            $dataseriesLabels1,                             // plotLabel
            $xAxisTickValues,                               // plotCategory
            $dataSeriesValues1                              // plotValues
        );
        //  Set additional dataseries parameters
        //      Make it a vertical column rather than a horizontal bar graph
        $series1->setPlotDirection(\PHPExcel_Chart_DataSeries::DIRECTION_COL);


        //  Set the Data values for each data series we want to plot

        $dataSeriesValues2 = array(
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$18:$S$18', NULL, 19),
            new \PHPExcel_Chart_DataSeriesValues('Number', 'Worksheet!$B$19:$S$19', NULL, 19),
        );

        //  Build the dataseries
        $series2 = new \PHPExcel_Chart_DataSeries(
            \PHPExcel_Chart_DataSeries::TYPE_LINECHART,      // plotType
            \PHPExcel_Chart_DataSeries::GROUPING_STANDARD,   // plotGrouping
            range(0, count($dataSeriesValues2)-1),          // plotOrder
            $dataseriesLabels2,                             // plotLabel
            NULL,                                           // plotCategory
            $dataSeriesValues2                              // plotValues
        );

        $layout=new \PHPExcel_Chart_Layout();  
        $layout->setShowVal(true);
        //  Set the series in the plot area
        $plotarea = new \PHPExcel_Chart_PlotArea(null, array($series1, $series2));
        //  Set the chart legend
        $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_BOTTOM, $layout, false);

        $title = new \PHPExcel_Chart_Title($name['title']);

        $ytitle = new \PHPExcel_Chart_Title("数量");

        //  Create the chart
        $chart = new \PHPExcel_Chart(
            'chart1',       // name
            $title,         // title
            $legend,        // legend
            $plotarea,      // plotArea
            true,           // plotVisibleOnly
            0,              // displayBlanksAs
            NULL,           // xAxisLabel
            $ytitle         // yAxisLabel
        );

        //  Set the position where the chart should appear in the worksheet
        $chart->setTopLeftPosition("A31")->setBottomRightPosition("Z40"); //图表位置 
        //  Add the chart to the worksheet
        $objWorksheet->addChart($chart);


        //-----------------------------------
        $excel = 'Excel2007';  
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,$excel);  
        $objWriter->setIncludeCharts(true); //图表必须  

        $filename = $name['filename'].'.xlsx'; 
      
        if($type == "Excel5"){  
            header('Content-Type: application/vnd.ms-excel'); //excel2003  
        }else{  
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //excel2007  
        }  
        header('Content-Disposition: attachment;filename="'.$filename.'"');  
        header('Cache-Control: max-age=0');
      
        /*解决Excel2007不能导出*/   
        $filePath = dirname(__FILE__) . rand(0, getrandmax()) . rand(0, getrandmax()) . ".tmp";
        $objWriter->save($filePath);  
        readfile($filePath);  
        unlink($filePath);  
    }

    // 多图表折线
    public function UserBt($expTitle,$expCellName,$expTableData,$name){
        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $objWorksheet = $objPHPExcel->getActiveSheet();

        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $xlsTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        // $objWorksheet->fromArray($data);

        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName['17'].'1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1',$expTitle);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);// 居中 

        $heBing = 4;
        for($z=0;$z<$dataNum;$z++){
            for($i=0;$i<$cellNum;$i++){
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
                $objPHPExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth(4.5);
            }
        // Miscellaneous glyphs, UTF-8
            $objPHPExcel->getActiveSheet(0)->mergeCells('A'.$heBing.':N'.($heBing+3));//合并单元格
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$heBing, '总结：'.$name['content'][$z]);
            $objPHPExcel->getActiveSheet()->getStyle('A'.$heBing)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);//对齐

            
        // for($i=0;$i<$dataNum;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$j].($heBing-2), $expCellName[$j][1]);
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($heBing-1), $expTableData[$z][$expCellName[$j][0]]);
            }
            $heBing += 6;
        }
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(6);

        $objPHPExcel->getActiveSheet(0)->mergeCells('A'.(6*$dataNum+2).':R'.(6*$dataNum+5));//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.(6*$dataNum+2), $name['zongjie']);
        $objPHPExcel->getActiveSheet()->getStyle('A'.(6*$dataNum+2))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);//对齐
        // $objPHPExcel->getActiveSheet(0)->mergeCells('A4:M7');//合并单元格
        // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A41', $expTitle);//写字
        // $objPHPExcel->getActiveSheet()->getStyle('A41')->getAlignment()->setWrapText(true);//自动换行

        // $objPHPExcel->getActiveSheet()->getRowDimension('41')->setRowHeight(75);//设置某一行的高度
        
        // 设置为文本
        // $objPHPExcel->getActiveSheet()->getStyle('27')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        // $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);// 居中  
        // $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);// 单元格宽度
        //  Set the Labels for each data series we want to plot
        $heBing = 2;
        for ($i=0; $i < $dataNum; $i++) { 
          
             //图表  
            $labels = array(  
                new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$A$3',10,1),//一班  
            );  
            $xLabels = array(  
                new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$C$2:$N$2',null,12),//取x轴刻度  
            );  
            $datas = array(  
                new \PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$C$'.($heBing+1).':$N$'.($heBing+1),null,12),//取一班数据  
            );  
          
            $series = array(  
                new \PHPExcel_Chart_DataSeries(  
                    \PHPExcel_Chart_DataSeries::TYPE_LINECHART,  
                    \PHPExcel_Chart_DataSeries::GROUPING_STANDARD,  
                    range(0, count($labels)-1),  
                    $labels,  
                    $xLabels,  
                    $datas  
                )  
            ); //图表框架  
          
            $layout=new \PHPExcel_Chart_Layout();  
            $layout->setShowVal(true);

            $areas = new \PHPExcel_Chart_PlotArea(null,$series);  
            // $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_BOTTOM,$layout,false); 

            $title = new \PHPExcel_Chart_Title($name['title']);  
            // $ytitle = new \PHPExcel_Chart_Title("人数");
            $chart = new \PHPExcel_Chart('line_chart',$title,$legend,$areas,true,false,null,$ytitle);
            $chart->setTopLeftPosition("O".$heBing)->setBottomRightPosition("S".($heBing + 5)); //图表位置  
            $heBing += 6;
            $objWorksheet->addChart($chart);  

        }


        $excel = 'Excel2007';  
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,$excel);  
        $objWriter->setIncludeCharts(true); //图表必须  

        $filename = $name['filename'].'.xlsx';  
      
        if($type == "Excel5"){  
            header('Content-Type: application/vnd.ms-excel'); //excel2003  
        }else{  
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //excel2007  
        }  
        header('Content-Disposition: attachment;filename="'.$filename.'"');  
        header('Cache-Control: max-age=0');
      
        /*解决Excel2007不能导出*/   
        $filePath = dirname(__FILE__) . rand(0, getrandmax()) . rand(0, getrandmax()) . ".tmp";
        $objWriter->save($filePath);  
        readfile($filePath);  
        unlink($filePath);  
    }

    // 三图折线
    public function MouseBt($expTitle,$expCellName,$expTableData,$name){
        vendor("PHPExcel.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $objWorksheet = $objPHPExcel->getActiveSheet();

        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $xlsTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        // dump($cellName[$cellNum-1]);exit();
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:B1');//合并单元格
        $objPHPExcel->getActiveSheet(0)->mergeCells('C1:D1');//合并单元格
        $objPHPExcel->getActiveSheet(0)->mergeCells('E1:F1');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', $expTitle);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1', $expTitle);
        $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);// 居中
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(4.5);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(4.5);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(4.5);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(4.5);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(4.5);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(4.5);

        // $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(4.5);
        // $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(4.5);
        // $heBing = 0;
        // for ($z=0; $z < 3; $z++) { 

        //     for($i=0;$i<$cellNum;$i++){
        //         $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i+$heBing].'2', $expCellName[$i][1]);
        //     }

        //     for($i=0;$i<$dataNum;$i++){
        //         for($j=0;$j<$cellNum;$j++){
        //             $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j+$heBing].($i+3), $expTableData[$i][$expCellName[$j][0]]);
        //         }
        //     }
        //     $heBing += 3;
        // }


        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
        }

        for($i=0;$i<40;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
            }
        }

        for($i=2;$i<4;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i-2][1]);
        }

        for($i=40;$i<80;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j+2].($i+3-40), $expTableData[$i][$expCellName[$j][0]]);
            }
        }

        for($i=4;$i<6;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i-4][1]);
        }

        for($i=80;$i<120;$i++){
            for($j=0;$j<$cellNum;$j++){
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j+4].($i+3-80), $expTableData[$i][$expCellName[$j][0]]);
            }
        }
        $objPHPExcel->getActiveSheet(0)->mergeCells('A43:P46');//合并单元格
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A43',$name['content']);
        $objPHPExcel->getActiveSheet()->getStyle('A43')->getAlignment()->setWrapText(true);//自动换行
        $objPHPExcel->getActiveSheet()->getStyle('A43')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY)->setVertical(\PHPExcel_Style_Alignment::VERTICAL_TOP);//对齐
        $heBing = 4;$val=1;
        for ($i=0; $i < 3; $i++) { 

             //图表  
            $labels = array(  
                new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$A$2',null,1),// 
            );  
            $xLabels = array(  
                new \PHPExcel_Chart_DataSeriesValues('String','Worksheet!$'.$cellName[$val-1].'$3:$'.$cellName[$val-1].'$42',null,40),//取x轴刻度  
            );  
            $datas = array(  
                new \PHPExcel_Chart_DataSeriesValues('Number','Worksheet!$'.$cellName[$val].'$3:$'.$cellName[$val].'$42',null,40),//取数据  
            );  
          
            $series = array(  
                new \PHPExcel_Chart_DataSeries(  
                    \PHPExcel_Chart_DataSeries::TYPE_LINECHART,  
                    \PHPExcel_Chart_DataSeries::GROUPING_STANDARD,  
                    range(0, count($labels)-1),  
                    $labels,  
                    $xLabels,  
                    $datas  
                )  
            ); //图表框架  
          
            $layout=new \PHPExcel_Chart_Layout();  
            $layout->setShowVal(true);  
            $areas = new \PHPExcel_Chart_PlotArea($layout,$series);  
            $legend = new \PHPExcel_Chart_Legend(\PHPExcel_Chart_Legend::POSITION_BOTTOM,$layout,false);  
            $title = new \PHPExcel_Chart_Title($name['title']);
            // $ytitle = new \PHPExcel_Chart_Title("数量");  
            $chart = new \PHPExcel_Chart('line_chart',$title,$legend,$areas,true,false,null,$ytitle);  
 
            $chart->setTopLeftPosition("G".$heBing)->setBottomRightPosition("Q".($heBing + 10)); //图表位置  
            $heBing += 13;
            $val+=2;
            $objWorksheet->addChart($chart);  
        }


        $excel = 'Excel2007';  
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,$excel);  
        $objWriter->setIncludeCharts(true); //图表必须  

        $filename = $name['filename'].'.xlsx';  
      
        if($type == "Excel5"){  
            header('Content-Type: application/vnd.ms-excel'); //excel2003  
        }else{  
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); //excel2007  
        }  
        header('Content-Disposition: attachment;filename="'.$filename.'"');  
        header('Cache-Control: max-age=0');
      
        /*解决Excel2007不能导出*/   
        $filePath = dirname(__FILE__) . rand(0, getrandmax()) . rand(0, getrandmax()) . ".tmp";
        $objWriter->save($filePath);  
        readfile($filePath);  
        unlink($filePath); 
    }
}
